package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import model.Manufacturer;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

@Service("manufacturerDAO")
public class ManufacturerDAO implements IManufacturerDAO {
	public static final String INSERT = "INSERT INTO MANUFACTURERS "
			+ "(U_ID,NAME,DESCRIPTION,ADDRESS,TEL,WEBSITE) "
			+ "VALUES(?,?,?,?,?,?)";
	public static final String UPDATE = "UPDATE MANUFACTURERS "
			+ "SET NAME=?,DESCRIPTION=?,ADDRESS=? , TEL=? , WEBSITE=? " + "WHERE ID=?";
	public static final String DELETE = "DELETE FROM MANUFACTURERS " + "WHERE ID=?";
	public static final String DELETE_BY_UID = "DELETE FROM MANUFACTURERS " 
		+ "WHERE U_ID=?";

	public static final String ALL_MANU = "SELECT * " + "From MANUFACTURERS ";
	
	public static final String ALL_MANU_NAME = "SELECT NAME " + "From MANUFACTURERS";

	public static final String READ_MANUFACTURERS_BY_U_ID = "SELECT * " + "FROM MANUFACTURERS "
			+ "WHERE U_ID = ?";
	
	public static final String READ_MANUFACTURER_BY_ID = "SELECT * " + "FROM MANUFACTURERS "
	+ "WHERE ID = ?";

	public static final String READ_MANUFACTURER_BY_NAME = 
		"SELECT * " + 
		"FROM MANUFACTURERS "+
		"WHERE REGEXP_LIKE(name,?,'i')";
	
	public static final String READ_MANUFACTURER_ADDRESS = "SELECT * " + "FROM MANUFACTURERS "
	+ "WHERE ADDRESS LIKE ?";
	public static final String READ_MANUFACTURER_WEBSITE = "SELECT * " + "FROM MANUFACTURERS "
	+ "WHERE WEBSITE = ?";

	@Resource
	private JdbcTemplate jdbcTemplate;

	public void addManuFacturer(
			final String u_id, 
			final String name, 
			final String description, 
			final String address, 
			final String tel,
			final String website){
		PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement pst = connection.prepareStatement(INSERT,Statement.RETURN_GENERATED_KEYS);
				pst.setString(1, u_id);
				pst.setString(2, name);
				pst.setString(3, description);
				pst.setString(4, address);
				pst.setString(5, tel);
				pst.setString(6, website);
				return pst;
			}
		};
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(preparedStatementCreator, keyHolder);
	}

	public Manufacturer getManufacturerById(final int id) {
		PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement pst = connection.prepareStatement(READ_MANUFACTURER_BY_ID);
				pst.setInt(1, id);
				return pst;
			}
		};
		List<Manufacturer> result = jdbcTemplate.query(preparedStatementCreator,
				new RowMapper<Manufacturer>() {
					@Override
					public Manufacturer mapRow(ResultSet resultSet, int i)
							throws SQLException {
						return new Manufacturer(
								resultSet.getString("id"),
								resultSet.getString("u_id"),
								resultSet.getString("name"),
								resultSet.getString("description"),
								resultSet.getString("address"),
								resultSet.getString("tel"), 
								resultSet.getString("website"));
					}
				});
		return result.get(0);
	}
	
	public List<String> getIdManufacturerViaName(final String name) {
		PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement pst = connection.prepareStatement(READ_MANUFACTURER_BY_NAME);
				pst.setString(1, name);
				return pst;
			}
		};
		List<Manufacturer> result = jdbcTemplate.query(preparedStatementCreator,
				new RowMapper<Manufacturer>() {
					@Override
					public Manufacturer mapRow(ResultSet resultSet, int i)
							throws SQLException {
						return new Manufacturer(
								resultSet.getString("id"),
								resultSet.getString("u_id"),
								resultSet.getString("name"),
								resultSet.getString("description"),
								resultSet.getString("address"),
								resultSet.getString("tel"), 
								resultSet.getString("website"));
					}
				});
		System.out.println("result manuDao:"+result.size());
		List<String> temp = new ArrayList<String>();
		temp.clear();
		if(result.size() != 0){
			for(Manufacturer manu:result){
				temp.add(manu.getId());
			}
			
		}
		return temp;
	}
	
	public List<String> getNameManufacturerMatched(String query) {
		PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement pst = connection.prepareStatement(ALL_MANU_NAME);
				return pst;
			}
		};
		List<String> result = jdbcTemplate.query(preparedStatementCreator,
				new RowMapper<String>() {
					@Override
					public String mapRow(ResultSet resultSet, int i)
							throws SQLException {
						return resultSet.getString("name");
					}
				});
		List<String> matched = new ArrayList<String>();
		String strTemp = null;
		System.out.println("size:"+result.size());
		query = query.toLowerCase();
		for(int i=0; i < result.size(); i++) {
            strTemp = result.get(i).toLowerCase();
            if(strTemp.contains(query)) {
                matched.add(result.get(i));
            }
        }
		System.out.println(matched.size());
		return matched;
	}
	
	public List<Manufacturer> getAllManufacturer(){
		List<Manufacturer> result = jdbcTemplate.query(ALL_MANU,
				new RowMapper<Manufacturer>() {
					@Override
					public Manufacturer mapRow(ResultSet resultSet, int i)
							throws SQLException {
						return new Manufacturer(
								resultSet.getString("id"),
								resultSet.getString("u_id"),
								resultSet.getString("name"),
								resultSet.getString("description"),
								resultSet.getString("address"),
								resultSet.getString("tel"), 
								resultSet.getString("website"));
					}
				});
		return result;
	}
	

	public List<Manufacturer> getManuFactureByUId(final String u_id) {
		PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement pst = connection
						.prepareStatement(READ_MANUFACTURERS_BY_U_ID);
				pst.setString(1, u_id);
				return pst;
			}
		};
		List<Manufacturer> result = jdbcTemplate.query(preparedStatementCreator,
				new RowMapper<Manufacturer>() {
					@Override
					public Manufacturer mapRow(ResultSet resultSet, int i)
							throws SQLException {
						return new Manufacturer(
								resultSet.getString("id"), 
								resultSet.getString("u_id"), 
								resultSet.getString("name"), 
								resultSet.getString("description"),
								resultSet.getString("address") , 
								resultSet.getString("tel"), 
								resultSet.getString("website"));
					}
				});
		if (result.size() == 0) {
			return null;
		}
		return result;
	}
	
	public void deleteManufacturer(final String id) {
		PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement pst = connection.prepareStatement(DELETE);
				pst.setString(1, id);
				return pst;
			}
		};
		jdbcTemplate.update(preparedStatementCreator);
	}
	
	
	public void deleteManufacturerByUID(final int id) {
		PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement pst = connection.prepareStatement(DELETE_BY_UID);
				pst.setInt(1, id);
				return pst;
			}
		};
		jdbcTemplate.update(preparedStatementCreator);
	}
	
	public void updateManufacturer(
			final String name,
			final String description,
			final String address,
			final String tel,
			final String website,
			final String id
			) {
		PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement pst = connection
						.prepareStatement(UPDATE);
				pst.setString(1, name);
				pst.setString(2, description);
				pst.setString(3, address);
				pst.setString(4, tel);
				pst.setString(5, website);
				pst.setInt(6, Integer.parseInt(id));
				return pst;
			}
		};
		System.out.println(id);
		jdbcTemplate.update(preparedStatementCreator);
	}
}
